---
title: SQLite state schema
sidebar:
  order: 2
---

import { Tabs, TabItem } from '@astrojs/starlight/components';
import SchemaSnippet from '../../../_assets/code/reference/state/sqlite-schema/schema.ts?snippet'
import ClientDocumentBasicSnippet from '../../../_assets/code/reference/state/sqlite-schema/columns/client-document-basic.tsx?snippet'
import ClientDocumentKvSnippet from '../../../_assets/code/reference/state/sqlite-schema/columns/client-document-kv.tsx?snippet'
import JsonStructSnippet from '../../../_assets/code/reference/state/sqlite-schema/columns/json-struct.ts?snippet'
import TableBasicSnippet from '../../../_assets/code/reference/state/sqlite-schema/columns/table-basic.ts?snippet'

LiveStore provides a schema definition language for defining your database tables and mutation definitions using explicit column configurations. LiveStore automatically migrates your database schema when you change your schema definitions.

> **Alternative Approach**: You can also define tables using [Effect Schema with annotations](/building-with-livestore/state/sqlite-schema-effect) for type-safe schema definitions.

### Example

<Tabs syncKey="package-manager">
  <TabItem label="schema.ts">
    <SchemaSnippet />
  </TabItem>
</Tabs>

## Defining tables

Define SQLite tables using explicit column definitions:

<TableBasicSnippet />

Use the optional `indexes` array to declare secondary indexes or enforce uniqueness (set `isUnique: true`).

## Column types

You can use these column types when defining tables:

### Core SQLite column types

- `State.SQLite.text`: A text field, returns `string`.
- `State.SQLite.integer`: An integer field, returns `number`.
- `State.SQLite.real`: A real field (floating point number), returns `number`.
- `State.SQLite.blob`: A blob field (binary data), returns `Uint8Array`.

### Higher level column types

- `State.SQLite.boolean`: An integer field that stores `0` for `false` and `1` for `true` and returns a `boolean`.
- `State.SQLite.json`: A text field that stores a stringified JSON object and returns a decoded JSON value.
- `State.SQLite.datetime`: A text field that stores dates as ISO 8601 strings and returns a `Date`.
- `State.SQLite.datetimeInteger`: A integer field that stores dates as the number of milliseconds since the epoch and returns a `Date`.

### Custom column schemas

You can also provide a custom schema for a column which is used to automatically encode and decode the column value.

### Example: JSON-encoded struct

<JsonStructSnippet />

### Schema migrations

Migration strategies:

- `auto`: Automatically migrate the database to the newest schema and rematerializes the state from the eventlog.
- `manual`: Manually migrate the database to the newest schema.

## Client documents

- Meant for convenience
- Client-only
- Goal: Similar ease of use as `React.useState`
- When schema changes in a non-backwards compatible way, previous events are dropped and the state is reset
  - Don't use client documents for sensitive data which must not be lost
- Implies
  - Table with `id` and `value` columns
  - `${MyTable}Set` event + materializer (which are auto-registered)

### Basic usage

<ClientDocumentBasicSnippet />

### KV-style client document

Sometimes you want a simple key-value store for arbitrary values without partial merging. You can model this by using `Schema.Any` as the value schema. With `Schema.Any`, updates fully replace the stored value (no partial merge semantics).

<ClientDocumentKvSnippet />

## Column types

You can use these column types:

#### Core SQLite column types

- `State.SQLite.text`: A text field, returns `string`.
- `State.SQLite.integer`: An integer field, returns `number`.
- `State.SQLite.real`: A real field (floating point number), returns `number`.
- `State.SQLite.blob`: A blob field (binary data), returns `Uint8Array`.

#### Higher level column types

- `State.SQLite.boolean`: An integer field that stores `0` for `false` and `1` for `true` and returns a `boolean`.
- `State.SQLite.json`: A text field that stores a stringified JSON object and returns a decoded JSON value.
- `State.SQLite.datetime`: A text field that stores dates as ISO 8601 strings and returns a `Date`.
- `State.SQLite.datetimeInteger`: A integer field that stores dates as the number of milliseconds since the epoch and returns a `Date`.

#### Custom column schemas

You can also provide a custom schema for a column which is used to automatically encode and decode the column value.

#### Example: JSON-encoded struct

<JsonStructSnippet />

## Best practices

### Column configuration

- Use appropriate SQLite column types for your data (text, integer, real, blob)
- Set `primaryKey: true` for primary key columns
- Use `nullable: true` for columns that can contain NULL values
- Provide meaningful `default` values where appropriate
- Add unique constraints via table `indexes` using `isUnique: true`

### Schema design

- Choose column types that match your data requirements
- Use custom schemas with `State.SQLite.json()` for complex data structures
- Group related table definitions in the same module
- Use descriptive table and column names

### General practices

- It's usually recommend to **not distinguish** between app state vs app data but rather keep all state in LiveStore.
	- This means you'll rarely use `React.useState` when using LiveStore
- In some cases for "fast changing values" it can make sense to keep a version of a state value outside of LiveStore with a reactive setter for React and a debounced setter for LiveStore to avoid excessive LiveStore mutations. Cases where this can make sense can include:
  - Text input / rich text editing
  - Scroll position tracking, resize events, move/drag events
  - ...
